MySQL 连接查询 |
您所在的位置：网站首页 › inner join on 多个条件 or 改写 › MySQL 连接查询 |
MySQL 连接查询
 在这里插入图片描述1.什么是连接查询在关系型数据库管理系统（RDBMS）中，连接查询是一项重要的数据库操作，它允许我们从多个表中检索和组合数据，以便进行更复杂的查询和分析。 比如员工的个人信息存储在 employee 表中，部门相关的信息存储在 department 表中，同时 employee 表中存在一个外键字段（dept_id），引用了 department 表的主键字段。 因此，当我们想要查看员工的个人信息以及他/她所在的部门信息，就需要同时查询 employee 和 department 表中的信息。此时，我们需要使用连接查询。连接查询（JOIN）可以基于两个表中的连接字段将数据行拼接到一起，返回两表中的相关数据。 2.连接类型MySQL 支持 SELECT 语句以及多表 DELETE 和 UPDATE 语句中使用 JOIN。 MySQL 支持多种类型的 JOIN： 内连接（INNER JOIN）交叉连接（CROSS JOIN）左连接（LEFT JOIN）右连接（RIGHT JOIN）自然连接（NATURAL JOIN）不管是哪种连接，本质上都是在总的笛卡尔积下进行筛选过滤。 内连接内连接写作 JOIN 或 INNER JOIN。 内连接返回两个表中满足连接条件的记录。  在这里插入图片描述代码语言：javascript复制SELECT columns
FROM
table1 INNER JOIN table2 ON table1.column = table2.column;交叉连接交叉连接写作 CROSS JOIN。 实际上，在 MySQL 中（仅限于 MySQL）CROSS JOIN 与 JOIN 和 INNER JOIN 的表现是一样的，在不指定 ON 条件得到的结果都是笛卡尔积，反之取两个表各自匹配的结果。  在这里插入图片描述代码语言：javascript复制SELECT columns
FROM
table1 CROSS JOIN table2;因为交叉连接无法使用维恩图进行描述，所以这里不用维恩图（Venn Diagram）表示 CROSS JOIN 的结果。 左连接左连接写作 LEFT JOIN 或 LEFT OUTER JOIN。 左连接返回左表中所有记录，以及与右表中满足连接条件的记录。如果右表中没有匹配的记录，对应位置将显示为 NULL。  在这里插入图片描述代码语言：javascript复制SELECT columns
FROM
table1 LEFT JOIN table2 ON table1.column = table2.column;右连接右连接写作 RIGHT JOIN 或 RIGHT OUTER JOIN。 右连接与左连接类似，但是返回右表中所有记录，以及与左表中满足连接条件的记录。如果左表中没有匹配的记录，对应位置将显示为 NULL。  在这里插入图片描述代码语言：javascript复制SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;RIGHT JOIN 的工作方式与 LEFT JOIN 类似。 为了保持代码在数据库之间的可移植性，建议您使用 LEFT JOIN 而不是 RIGHT JOIN。 自然连接使用 NATURAL 关键字与其他类型的 JOIN 组合表示自然连接。 代码语言：javascript复制NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN自然连接相当于不能指定连接条件的连接，MySQL 会使用左右表内相同名字和类型的字段作为连接条件。也就是说 NATURAL JOIN 两个表，与使用 USING 子句指定两个表所有同名列的 JOIN 在语义上等价。 假设 t1 表有 i 和 j 列，t2 表有 k 和 j 列，那么下面两个 JOIN 查询是等价的： 代码语言：javascript复制SELECT * FROM t1 NATURAL JOIN t2; SELECT * FROM t1 JOIN t2 USING (j);3.连接条件连接表我们可以用两个关键字 ON 和 USING 指定连接条件。 与 ON 一起使用的 search_condition 是可在 WHERE 子句中使用的任何形式的条件表达式。 通常，ON 子句用于指定如何连接表的条件，而 WHERE 子句则限制结果集中包含哪些行。 USING(join_column_list) 子句指定两个表中都必须存在的列的列表。 如果表 a 和 b 都包含列 c1、c2 和 c3，则以下连接分别使用 USING 和 ON 指定连接条件是等价的。 代码语言：javascript复制a LEFT JOIN b USING (c1, c2, c3) a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3关于确定哪些行满足连接条件，两个连接在语义上是相同的。 但是关于确定为 SELECT * 显示哪些列，这两个联接在语义上并不相同。 USING 连接选择相应列的合并值，而 ON 连接选择所有表中的所有列。 对于 USING 连接，SELECT * 选择以下值： 代码语言：javascript复制COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)对于 ON 连接，SELECT * 选择以下值： 代码语言：javascript复制a.c1, a.c2, a.c3, b.c1, b.c2, b.c34.隐式连接使用逗号连接表当使用逗号而不是 JOIN 连接表时，为隐式连接。 如果指定条件，相当于 INNER JOIN。如果不指定条件，相当于执行 CROSS JOIN。 以 a 和 b 表为例，测试一下。 代码语言：javascript复制SELECT * FROM a; +------+------+ | id | col | +------+------+ | 1 | 11 | | 2 | 12 | +------+------+ SELECT * FROM b; +------+------+ | id | col | +------+------+ | 2 | 22 | | 3 | 23 | +------+------+ # 隐式连接不指定条件，相当于执行 CROSS JOIN SELECT * FROM a, b; +------+------+------+------+ | id | col | id | col | +------+------+------+------+ | 1 | 11 | 2 | 22 | | 2 | 12 | 2 | 22 | | 1 | 11 | 3 | 23 | | 2 | 12 | 3 | 23 | +------+------+------+------+ # 隐式连接指定条件，相当于执行 INNER JOIN SELECT * FROM a,b WHERE a.id=b.id; +------+------+------+------+ | id | col | id | col | +------+------+------+------+ | 2 | 12 | 2 | 22 | +------+------+------+------+ # 显示执行 INNER JOIN SELECT* FROM a JOIN b ON a.id=b.id; +------+------+------+------+ | id | col | id | col | +------+------+------+------+ | 2 | 12 | 2 | 22 | +------+------+------+------+逗号是隐式连接运算符。隐式连接是SQL92中的标准内容，而在SQL99中显式连接才是标准，虽然很多人还在用隐私连接，但是它已经从标准中被移除。推荐使用显示连接，因为可以更清楚地显示多个表之间连接关系和连接依赖的属性。 逗号与 JOIN 的优先级当逗号与 JOIN 同时使用时，JOIN 的优先级高于逗号运算符 (,)。因此，连接表达式 t1, t2 JOIN t3 被解释为 (t1, (t2 JOIN t3))，而不是 ((t1, t2) JOIN t3)。这会影响 ON 子句，因为该子句只能引用连接表中的列。 代码语言：javascript复制CREATE TABLE t1 (i1 INT, j1 INT); CREATE TABLE t2 (i2 INT, j2 INT); CREATE TABLE t3 (i3 INT, j3 INT); INSERT INTO t1 VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); INSERT INTO t3 VALUES(1, 1); SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);JOIN 优先于逗号运算符，因此 ON 子句的操作数是 t2 和 t3。 由于 t1.i1 不是任一操作数中的列，因此结果将报错Unknown column 't1.i1' in 'on clause'。 如果想使上面的查询正确执行，可以采取下面两个措施： 使用括号将 t1, t2 显示组合在一起。代码语言：javascript复制SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);避免使用逗号运算符并使用 JOIN 代替。代码语言：javascript复制SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);5.全外连接全外连接写作 FULL JOIN 或 FULL OUTER JOIN。 外连接是求两个集合的并集。从笛卡尔积的角度讲就是从笛卡尔积中挑出 ON 子句条件成立的记录，然后加上左表中剩余的记录，最后加上右表中剩余的记录。  在这里插入图片描述MySQL 不支持全外连接，但是我们可以对左连接和右连接的结果做 UNION 操作（会去除重复行）来实现。 6.小结连接查询是MySQL强大而常用的功能，它允许我们从多个表中检索和组合数据，以满足复杂的查询需求。 MySQL支持多种连接类型，包括 INNER JOIN、CROSS JOIN、LEFT JOIN、RIGHT JOIN 和 NATURAL JOIN，每种类型都有其特定的用途和语法。 通过理解连接查询的基本概念和 MySQL 支持的连接类型，你可以更好地利用 MySQL 来处理复杂的数据查询和分析任务，提高数据库应用的灵活性和功能性。 参考文献MySQL 8.0 Reference Manual :: 13.2.13.2 JOIN Clause |
【本文地址】
今日新闻 |
推荐新闻 |